﻿
using Microsoft.SharePoint.Client;

using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Drawing.Design;
using System.Linq;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Reflection;

[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("PublicPrivateKeyFile.snk")]

namespace SharepointOnlineSource
{
    [DtsPipelineComponent(DisplayName = "SharepointOnlineSource"
                        , Description = "Source Component to fetch data from sharepoint online"
                        , ComponentType = ComponentType.SourceAdapter
                        , IconResource = "SharepointOnlineSource.source16.ico")]

    public class SharepointOnlineSource : PipelineComponent
    {
        public override void ProvideComponentProperties()
        {
            base.RemoveAllInputsOutputsAndCustomProperties();
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

            ComponentMetaData.Name = "SharepointOnlineSource";
            ComponentMetaData.Description = "Source Component to fetch data from sharepoint online";
            ComponentMetaData.ContactInfo = "dilkushp@hotmail.com";
            IDTSOutput100 outputObject = ComponentMetaData.OutputCollection.New();
            outputObject.Name = "OutputFromSharepointOnlineSource";

            IDTSCustomProperty100 SharepointServer = ComponentMetaData.CustomPropertyCollection.New();
            SharepointServer.Name = "Server";
            SharepointServer.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            SharepointServer.Description = "name of sharepoimnt server <mysite>.sharepoint.com";
            SharepointServer.Value = "https://<mysite>@sharepoint.com";
            //SharepointServer.ID = 1;

            IDTSCustomProperty100 User_Name = ComponentMetaData.CustomPropertyCollection.New();
            User_Name.Name = "User";
            User_Name.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            User_Name.Description = "USER nAME FOR sHAREPOINT ONLINE AUTHENTICATION";
            User_Name.Value = "<user>@<my site>.microsoft.com";
            //User_Name.ID = 2;

            IDTSCustomProperty100 Password = ComponentMetaData.CustomPropertyCollection.New();
            Password.Name = "Password";
            Password.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            Password.EncryptionRequired = true;
            Password.Description = "Password for sharepoint online user";
            Password.Value = "<Password>";
            //Password.ID = 3;


            IDTSCustomProperty100 ListName = ComponentMetaData.CustomPropertyCollection.New();
            ListName.Name = "List name";
            ListName.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            ListName.Description = "Name of HSraepoint list we need to fetch";
            ListName.Value = "<List>";
            //ListName.ID = 4;


            IDTSCustomProperty100 ReadOnly = ComponentMetaData.CustomPropertyCollection.New();
            ReadOnly.Name = "ReadOnly Columns";
            ReadOnly.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
            ReadOnly.Description = "Do you want read only columns as well?";
            ReadOnly.Value = "No";



        }

        ClientContext cont;
        Web web;
        string temp_server, temp_user, temp_password, temp_listname;

        public void getconnection(string server, string username, string password)
        {
            //if (cont == null)
            //{
            cont = new ClientContext(server);
            SecureString Password = new SecureString();
            foreach (char c in password.ToCharArray())
            {
                Password.AppendChar(c);
            }
            cont.Credentials = new SharePointOnlineCredentials(username, Password);
            // }
        }

        public List<Field> getaccessiblecolumns(string server, string username, string password, string listname)
        {
            string readonly_y = ComponentMetaData.CustomPropertyCollection[4].Value;

            getconnection(server, username, password);
            Web web = cont.Web;
            cont.Load(web);
            cont.ExecuteQuery();

            List sstest = web.Lists.GetByTitle(listname);
            var fs = sstest.Fields;
                //cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments)));
            if(readonly_y.ToUpper() == "NO")
               cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.ReadOnlyField || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments) || field.InternalName == "ID"));
            else
                cont.Load(fs, fields => fields.Where(field => !(field.Hidden || field.Group == "_Hidden" || field.FieldTypeKind == FieldType.Attachments)));

            sstest.GetRelatedFields();
            cont.ExecuteQuery();

            return fs.ToList<Field>();
        }
        public override DTSValidationStatus Validate()
        {
            var status = DTSValidationStatus.VS_ISVALID;
            string server = ComponentMetaData.CustomPropertyCollection[0].Value;
            string user = ComponentMetaData.CustomPropertyCollection[1].Value;
            string password = ComponentMetaData.CustomPropertyCollection[2].Value;
            string listname = ComponentMetaData.CustomPropertyCollection[3].Value;
            string readonly_y = ComponentMetaData.CustomPropertyCollection[4].Value;


            string[] server_parts = server.Split('.');
            string[] user_parts = user.Split('@');

            if ((server_parts.Length < 3) || (server_parts[0].Equals("")) || (server_parts[1].Equals("")) || (server_parts[2].Equals("")))// || (!server_parts[1].ToLower().Equals("sharepoint")) || (!server_parts[2].ToLower().Contains("com")))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if ((user_parts.Length != 2) || (user_parts[0].Equals("")))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if (password.Equals(""))
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if (listname == "")
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if (readonly_y.ToUpper() != "NO" && readonly_y.ToUpper() != "YES")
            {
                status = DTSValidationStatus.VS_ISBROKEN;
            }
            else if ((server != temp_server || user != temp_user || password != temp_password || listname != temp_listname) && (server != "https://<mysite>@sharepoint.com" && listname != "<List>" && user == "<user>@<my site>.microsoft.com" && password == "<Password>"))
            {
                temp_listname = listname;
                temp_password = password;
                temp_server = server;
                temp_user = user;
                ReinitializeMetaData();
            }
            return status;
        }

        public override void ReinitializeMetaData()
        {
            IDTSOutput100 item;
            Dictionary<string, string> strs = new Dictionary<string, string>();
            if (base.ComponentMetaData.OutputCollection.Count != 0)
            {
                item = base.ComponentMetaData.OutputCollection[0];
                IEnumerable<IDTSOutputColumn100> collection = item.OutputColumnCollection.Cast<IDTSOutputColumn100>();
                IEnumerable<IDTSExternalMetadataColumn100> collection1 = item.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
                Func<IDTSOutputColumn100, int> externalMetadataColumnID = (IDTSOutputColumn100 col) => col.ExternalMetadataColumnID;
                Func<IDTSExternalMetadataColumn100, int> d = (IDTSExternalMetadataColumn100 metaCol) => metaCol.ID;
                var collection2 = collection.Join(collection1, externalMetadataColumnID, d, (IDTSOutputColumn100 col, IDTSExternalMetadataColumn100 metaCol) =>
                {
                    string name = col.Name;
                    return new { ColumnName = name, SpColName = (string)((object)metaCol.CustomPropertyCollection["Id"].Value) };
                });
                strs = collection2.ToDictionary((a) => a.SpColName, (a) => a.ColumnName);
                item.OutputColumnCollection.RemoveAll();
                item.ExternalMetadataColumnCollection.RemoveAll();
            }
            else
            {
                item = base.ComponentMetaData.OutputCollection.New();
                item.Name = "Public List Output";
                item.ExternalMetadataColumnCollection.IsUsed = true;
            }
            this.LoadDataSourceInformation(item, strs);
            base.ReinitializeMetaData();
        }

        private void LoadDataSourceInformation(IDTSOutput100 output, Dictionary<string, string> strs)
        {
            string value = base.ComponentMetaData.CustomPropertyCollection[0].Value;
            string obj = base.ComponentMetaData.CustomPropertyCollection[3].Value;
            if (value != null && obj != null)
            {
                IDTSOutput100 variable = output;
                string str = (string)((dynamic)base.ComponentMetaData.CustomPropertyCollection[0].Value);
                string value1 = (string)((dynamic)base.ComponentMetaData.CustomPropertyCollection[3].Value);
                this.CreateExternalMetaDataColumns(variable, str, value1, strs);
            }
        }

        private void CreateExternalMetaDataColumns(IDTSOutput100 output, string server, string list, Dictionary<string, string> existingColumnData)
        {

            try
            {
                List<Field> accessibleSharePointColumns = this.getaccessiblecolumns(server, ComponentMetaData.CustomPropertyCollection[1].Value, ComponentMetaData.CustomPropertyCollection[2].Value, list);


                List<Field> columnDatas = accessibleSharePointColumns;
                IEnumerable<IGrouping<string, Field>> groupings = columnDatas.GroupBy<Field, string>((Field n) => n.Title);
                IEnumerable<IGrouping<string, Field>> groupings1 = groupings.Where<IGrouping<string, Field>>((IGrouping<string, Field> g) => g.Count<Field>() > 1);
                IEnumerable<string> strs = groupings1.Select<IGrouping<string, Field>, string>((IGrouping<string, Field> g) => g.Key);

                foreach (Field accessibleSharePointColumn in columnDatas)
                {
                    IDTSExternalMetadataColumn100 item = output.ExternalMetadataColumnCollection.New();
                    if (existingColumnData.ContainsKey(accessibleSharePointColumn.Title) || existingColumnData.ContainsKey(accessibleSharePointColumn.EntityPropertyName))
                    {
                        if(accessibleSharePointColumn.ReadOnlyField)
                            item.Name = accessibleSharePointColumn.EntityPropertyName;
                        else
                            item.Name = accessibleSharePointColumn.Title;
                    }
                    else if (!strs.Contains<string>(accessibleSharePointColumn.Title))
                    {
                        item.Name = accessibleSharePointColumn.Title;
                    }
                    else
                    {
                        item.Name = string.Concat(accessibleSharePointColumn.Title, " (", accessibleSharePointColumn.Title, ")");
                    }
                    if (accessibleSharePointColumn.ReadOnlyField)
                        item.Description = accessibleSharePointColumn.EntityPropertyName;
                    else
                        item.Description = accessibleSharePointColumn.Title;
                    item.Length = 0;
                    item.Precision = 0;
                    item.Scale = 0;
                    if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_BOOL;
                    }
                    else if (accessibleSharePointColumn.FieldTypeKind.ToString() == "DateTime")
                    {
                        item.DataType = DataType.DT_DBTIMESTAMP;
                    }
                    else if ("Number|Currency".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_R8;
                    }
                    else if ("Counter|Integer".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_I4;
                    }
                    else if ("Guid".Contains(accessibleSharePointColumn.FieldTypeKind.ToString()))
                    {
                        item.DataType = DataType.DT_GUID;
                    }
                    else
                    {
                        item.DataType = DataType.DT_WSTR;
                        item.Length = 4000;
                    }
            
                    IDTSCustomProperty100 name = item.CustomPropertyCollection.New();
                    name.Name = "Id";
                    name.Description = accessibleSharePointColumn.EntityPropertyName;
                    if (accessibleSharePointColumn.ReadOnlyField)
                        name.Value = accessibleSharePointColumn.EntityPropertyName;
                    else
                        name.Value = accessibleSharePointColumn.Title;
                    IDTSOutputColumn100 desc = output.OutputColumnCollection.New();
                    desc.Name = item.Name;
                    desc.Description = item.Description;
                    desc.ExternalMetadataColumnID = item.ID;
                    IDTSCustomProperty100 value = desc.CustomPropertyCollection.New();
                    value.Name = name.Name;
                    value.Description = name.Description;
                    value.Value = name.Value;
                    desc.SetDataTypeProperties(item.DataType, item.Length, item.Precision, item.Scale, 0);


                }

            }
            catch (Exception ex)
            {
                output.ExternalMetadataColumnCollection.RemoveAll();
                throw;
            }
        }

        public override void PreExecute()
        {
            base.PreExecute();
            
            IEnumerable<IDTSOutputColumn100> collection = base.ComponentMetaData.OutputCollection[0].OutputColumnCollection.Cast<IDTSOutputColumn100>();
            IEnumerable<IDTSExternalMetadataColumn100> collection1 = base.ComponentMetaData.OutputCollection[0].ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
            Func<IDTSOutputColumn100, int> externalMetadataColumnID = (IDTSOutputColumn100 col) => col.ExternalMetadataColumnID;
            var collection2 = collection.Join(collection1, externalMetadataColumnID, (IDTSExternalMetadataColumn100 metaCol) => metaCol.ID, (IDTSOutputColumn100 col, IDTSExternalMetadataColumn100 metaCol) => new { Name = (string)((object)metaCol.CustomPropertyCollection["Id"].Value), BufferColumn = base.BufferManager.FindColumnByLineageID(base.ComponentMetaData.OutputCollection[0].Buffer, col.LineageID) });

            this._bufferLookup = collection2.ToDictionary((a) => a.Name, (a) => a.BufferColumn);
            IEnumerable<IDTSOutputColumn100> collection3 = base.ComponentMetaData.OutputCollection[0].OutputColumnCollection.Cast<IDTSOutputColumn100>();
            
            IEnumerable<IDTSExternalMetadataColumn100> collection4 = base.ComponentMetaData.OutputCollection[0].ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>();
            Func<IDTSOutputColumn100, int> func = (IDTSOutputColumn100 col) => col.ExternalMetadataColumnID;
            Func<IDTSExternalMetadataColumn100, int> d = (IDTSExternalMetadataColumn100 metaCol) => metaCol.ID;
            var collection5 = collection3.Join(collection4, func, d, (IDTSOutputColumn100 col, IDTSExternalMetadataColumn100 metaCol) => new { Name = (string)((object)metaCol.CustomPropertyCollection["Id"].Value), DataType = metaCol.DataType });
            this._bufferLookupDataType = collection5.ToDictionary((a) => a.Name, (a) => a.DataType);
        }

        public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
        {
            IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
            PipelineBuffer buffer = buffers[0];
            string server = ComponentMetaData.CustomPropertyCollection[0].Value;
            string user = ComponentMetaData.CustomPropertyCollection[1].Value;
            string password = ComponentMetaData.CustomPropertyCollection[2].Value;
            string listname = ComponentMetaData.CustomPropertyCollection[3].Value;
            getconnection(server, user, password);
            List<Field> fields = getaccessiblecolumns(server, user, password, listname);
            web = cont.Web;
            cont.Load(web);
            cont.ExecuteQuery();
            List sstest = web.Lists.GetByTitle(listname);
            CamlQuery query = CamlQuery.CreateAllItemsQuery();
            ListItemCollection items = sstest.GetItems(query);
            cont.Load(items);
            cont.ExecuteQuery();
            foreach (ListItem item in items)
            {
                buffer.AddRow();
                int i = 0;
                foreach (IDTSOutputColumn100 coll in output.OutputColumnCollection)
                {
                    try
                    {
                        List<Field> x = fields.Where(f => f.Title == coll.Name).ToList<Field>();
                        //System.IO.File.AppendAllText(@"C:\Temp\mydata.txt", item[x[0].InternalName] + " \n");

                        if (item[x[0].InternalName] != "")
                        {
                            //System.IO.File.AppendAllText(@"D:\dummy\mydata.txt",x[0].InternalName + " : " + item[x[0].InternalName].GetType().ToString() + "\n");
                            if (item[x[0].InternalName].GetType().ToString() == "Microsoft.SharePoint.Client.FieldUserValue[]")
                            {
                                Microsoft.SharePoint.Client.FieldUserValue[] val = (Microsoft.SharePoint.Client.FieldUserValue[])item[x[0].InternalName];
                                String data = "";
                                foreach (var xx in val)
                                {
                                    if(data!="")
                                    data = data + "|" + xx.LookupValue + ":" + xx.LookupId;
                                    else
                                        data = xx.LookupValue + ":" + xx.LookupId;
                                }
                                buffer[i] = data;
                                //buffer[i] = val.LookupValue +":" + val.LookupId;
                            }
                            else if (item[x[0].InternalName].GetType().ToString() == "Microsoft.SharePoint.Client.FieldUserValue")
                            {

                                Microsoft.SharePoint.Client.FieldUserValue val = (Microsoft.SharePoint.Client.FieldUserValue)item[x[0].InternalName];
                                buffer[i] = val.LookupValue +":" + val.LookupId;
                            }

                            else if (item[x[0].InternalName].GetType().ToString() == "Microsoft.SharePoint.Client.FieldUrlValue")
                            {
                                Microsoft.SharePoint.Client.FieldUrlValue val = (Microsoft.SharePoint.Client.FieldUrlValue)item[x[0].InternalName];
                                buffer[i] = val.Url + ":D:" + val.Description;
                            }
                            else if (item[x[0].InternalName].GetType().ToString() == "Microsoft.SharePoint.Client.FieldLookupValue")
                            {
                                Microsoft.SharePoint.Client.FieldLookupValue val = (Microsoft.SharePoint.Client.FieldLookupValue)item[x[0].InternalName];                                
                                buffer[i] = val.LookupValue + ":" + val.LookupId;
                            }
                            else if (item[x[0].InternalName].GetType().ToString() == "System.String[]")
                            {
                                string msg = "";
                                foreach (string s in (System.String[])item[x[0].InternalName])
                                {
                                    if (msg != "")
                                        msg = msg + "|" + s;
                                    else
                                        msg = s;
                                }
                                buffer[i] = msg;
                            }
                            else
                            {
                                buffer[i] = item[x[0].InternalName];
                            }
                        }
                        else
                        {
                            //System.IO.File.AppendAllText(@"C:\Temp\mydata.txt", "I was here \n");
                            buffer[i] = "";
                        }
                        i++;
                    }
                    catch (Exception ex)
                    {
                        i++;
                        //throw ex;
                    }
                }
            }
            buffer.SetEndOfRowset();

        }

        public FieldCollection fs { get; set; }

        public string query1 { get; set; }

        public Dictionary<string, int> _bufferLookup { get; set; }

        public Dictionary<string, DataType> _bufferLookupDataType { get; set; }
    }
}
